﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.Diagnostics;
using System.Linq;
using System.Security.Cryptography;
using System.Text;
using System.Threading.Tasks;

namespace LinqDemo
{
    internal class Program
    {
        static void Main(string[] args)
        {
            SelectManyWithLinq();
            Console.WriteLine("----------------------");
            SelectManyWithMethod();
            Console.WriteLine("----------------------");

            InnerJoinWithLinq();
            Console.WriteLine("----------------------");
            InnerJoinWithMethod();
            Console.WriteLine("----------------------");

            GroupJoinWithLinq();
            //GroupJoinWithLinq2();
            Console.WriteLine("----------------------");
            GroupJoinWithMethod();
            Console.WriteLine("----------------------");

            LeftJoinWithLinq();
            Console.WriteLine("----------------------");
            LeftJoinWithMethod();

            Console.ReadKey();
        }

        #region SelectMany
        static void SelectManyWithLinq()
        {
            Console.WriteLine("SelectManyWithLinq");
            // 1.结果为一层结构
            var emAndHo = from e in Employee.GetAllEmployees()
                          from h in e.Hobbies
                          orderby e.Id
                          select new { Id = e.Id, Name = e.Name, Hobby = h };
            foreach (var item in emAndHo)
            {
                Console.WriteLine($"员工ID:{item.Id},员工姓名:{item.Name},员工爱好:{item.Hobby}");
            }
            Console.WriteLine();

            // 2.结果为一层结构
            IEnumerable<string> emHo = from e in Employee.GetAllEmployees()
                                       from h in e.Hobbies
                                       orderby e.Id
                                       select h;
            foreach (var item in emHo)
            {
                Console.WriteLine($"员工爱好:{item}");
            }
            Console.WriteLine();

            // 3.结果为二层结构，对比2
            IEnumerable<List<string>> emHo2 = from e in Employee.GetAllEmployees()
                                              orderby e.Id
                                              select e.Hobbies;
            foreach (List<string> list in emHo2)
            {
                foreach (string item in list)
                {
                    Console.WriteLine($"员工爱好:{item}");
                }
            }
        }

        static void SelectManyWithMethod()
        {
            Console.WriteLine("SelectManyWithMethod");
            // 1.结果为一层结构
            var emAndHo = Employee.GetAllEmployees()
                .SelectMany(e => e.Hobbies, (e, h) => new { Employee = e, Hobby = h })
                .OrderBy(item => item.Employee.Id)
                .Select(item => new { Id = item.Employee.Id, Name = item.Employee.Name, Hobby = item.Hobby });
            foreach (var item in emAndHo)
            {
                Console.WriteLine($"员工ID:{item.Id},员工姓名:{item.Name},员工爱好:{item.Hobby}");
            }
            Console.WriteLine();

            // 2.结果为一层结构
            IEnumerable<string> emHo = Employee.GetAllEmployees().OrderBy(e => e.Id).SelectMany(e => e.Hobbies);//.Select(h => h);
            foreach (var item in emHo)
            {
                Console.WriteLine($"员工爱好:{item}");
            }
            Console.WriteLine();

            // 3.结果为二层结构，对比2
            IEnumerable<List<string>> emHo2 = Employee.GetAllEmployees().OrderBy(e => e.Id).Select(e => e.Hobbies);
            foreach (List<string> list in emHo2)
            {
                foreach (string item in list)
                {
                    Console.WriteLine($"员工爱好:{item}");
                }
            }
        }
        #endregion

        #region InnerJoin
        public static void InnerJoinWithLinq()
        {
            Console.WriteLine("InnerJoinWithLinq");
            var emDe = from e in Employee.GetAllEmployees()
                       join d in Department.GetAllDepartments() on e.DepartmentId equals d.Id
                       orderby e.Id
                       select new { Id = e.Id, Name = e.Name, DepartmentName = d.Name };
            foreach (var item in emDe)
            {
                Console.WriteLine($"员工ID:{item.Id},员工姓名:{item.Name},所属部门:{item.DepartmentName}");
            }
        }

        public static void InnerJoinWithMethod()
        {
            Console.WriteLine("InnerJoinWithMethod");
            var emDe = Employee.GetAllEmployees().Join(
                    Department.GetAllDepartments(),
                    e => e.DepartmentId,
                    d => d.Id,
                    (e, d) => new { Id = e.Id, Name = e.Name, DepartmentName = d.Name }
                ).OrderBy(em => em.Id);
            foreach (var item in emDe)
            {
                Console.WriteLine($"员工ID:{item.Id},员工姓名:{item.Name},所属部门:{item.DepartmentName}");
            }
        }

        #endregion

        #region GroupJoin
        static void GroupJoinWithLinq()
        {
            Console.WriteLine("GroupJoinWithLinq");
            var GroupJoinQS = from dept in Department.GetAllDepartments()
                              join emp in Employee.GetAllEmployees() on dept.Id equals emp.DepartmentId into EmployeeGroups
                              orderby dept.Id
                              select new { dept, EmployeeGroups };

            foreach (var item in GroupJoinQS)
            {
                Console.WriteLine("Department :" + item.dept.Name);
                foreach (var employee in item.EmployeeGroups)
                {
                    Console.WriteLine("  EmployeeID : " + employee.Id + " , Name : " + employee.Name);
                }
            }
        }

        static void GroupJoinWithLinq2()
        {
            Console.WriteLine("GroupJoinWithLinq");
            var GroupJoinQS = from dept in Department.GetAllDepartments()
                              join emp in Employee.GetAllEmployees() on dept.Id equals emp.DepartmentId into depm
                              orderby dept.Id
                              select depm;
            // depm实际上是IEnumerable<Employee>类型，即被关联对象列表
            // GroupJoinQS对象为IEnumerable<IEnumerable<Employee>>
            foreach (IEnumerable<Employee> item in GroupJoinQS)
            {
                //Console.WriteLine("Department :" + item.);
                foreach (Employee sub in item)
                {
                    Console.WriteLine("  EmployeeID : " + sub.Id + " , Name : " + sub.Name);
                }
            }
        }

        static void GroupJoinWithMethod()
        {
            Console.WriteLine("GroupJoinWithMethod");
            var GroupJoinMethod = Department.GetAllDepartments()
             .GroupJoin
             (
                 Employee.GetAllEmployees(),
                 dept => dept.Id,
                 emp => emp.DepartmentId,
                 (dept, emps) => new { dept, emps }
             ).OrderBy(item => item.dept.Id);
            foreach (var item in GroupJoinMethod)
            {
                Console.WriteLine("Department :" + item.dept.Name);
                foreach (var employee in item.emps)
                {
                    Console.WriteLine("  EmployeeID : " + employee.Id + " , Name : " + employee.Name);
                }
            }
        }
        #endregion

        #region LeftOutJoin
        static void LeftJoinWithLinq()
        {
            Console.WriteLine("LeftJoinWithLinq");
            var empdepts = from e in Employee.GetAllEmployees()
                          join d in Department.GetAllDepartments() on e.DepartmentId equals d.Id into depts
                          from dept in depts.DefaultIfEmpty()
                          orderby e.Id
                          select new { e.Id, e.Name, DeptName = dept?.Name };
            foreach (var item in empdepts)
            {
                Console.WriteLine($"员工ID:{item.Id},员工姓名:{item.Name},部门名称:{item.DeptName}");
            }
        }

        static void LeftJoinWithMethod()
        {
            Console.WriteLine("LeftJoinWithMethod");
            var empdepts = Employee.GetAllEmployees().GroupJoin(
                    Department.GetAllDepartments(),
                    e => e.DepartmentId,
                    d => d.Id,
                    (e, ds) => new { e.Id, e.Name, Departments = ds }
                );
            var results = empdepts.SelectMany(e => e.Departments.DefaultIfEmpty(), (e, d) => new { e.Id, e.Name, DeptName = d?.Name });
            foreach (var item in results)
            {
                Console.WriteLine($"员工ID:{item.Id},员工姓名:{item.Name},部门名称:{item.DeptName}");
            }
        }
        #endregion
    }
}
